create or replace procedure udm_p_thirdsalefeeexpand(v_policy_no  in varchar2,
                                                     v_endor_no   in varchar2,
                                                     v_endor_type in varchar2,
                                                     v_endor_sn   in varchar2,
                                                     v_sysday     in varchar2,
                                                     v_sys_source in varchar2) is
  v_mainid         varchar2(32);
  v_count          number(10) := 0;
  v_exist_count    number(10) := 0;
  v_f_count        number(10) := 0; --对冲负数个数
  v_z_count        number(10) := 0; --对冲正数个数
  v_fee_amount     number(18, 2) := 0;
  v_fee_amount_cny number(18, 2) := 0;
  v_fee_amountold  number(18, 2) := 0;
  v_fee_amountnew  number(18, 2) := 0;
begin
select count(1)
into v_count
from udm_policy_sales_fee
where endor_no = v_endor_no
  and fee_rate != 0;
--是否有对冲数据
select count(1)
into v_f_count
from UDM_P_THIRDSALES_FEEtemp
where HEDGE_FLAG = 'F'
  and endor_no = v_endor_no;
select count(1)
into v_z_count
from UDM_P_THIRDSALES_FEEtemp
where HEDGE_FLAG = 'Z'
  and endor_no = v_endor_no;
if v_f_count > 0 and v_z_count = 0 and v_count > 0 then
    insert into UDM_P_THIRDSALES_FEEtemp
      (id,
       main_id,
       second_id,
       policy_no,
       endor_no,
       crct_no,
       fee_type,
       operator_code,
       fee_rate,
       fee_amount,
       extax_fee_amount,
       tax_amount,
       currency,
       tax_rate,
       fee_amount_cny,
       extax_fee_amount_cny,
       tax_amount_cny,
       ccaltype,
       is_fixed_rate,
       hedge_sencondid,
       hedge_thirdid,
       taildiff_flag,
       HEDGE_FLAG)
select v_sys_source || v_sysday ||
       LPAD(SEQ_UDM_P_Tsalefeeid.Nextval, 12, '0') ID,
       a.id Main_ID,
       f.id,
       a.policy_no 保单号,
       a.endor_no 批单号,
       f.crct_no,
       f.fee_type,
       f.operator_code 业务员,
       f.fee_rate 费用比例,
       case
           when f.ccaltype = '0' then
               round(a.totalextax_premium_amount * f.fee_rate, 2)
           else
               round(a.totalpremium_amount * f.fee_rate, 2)
           end as 费用金额变化量交易币,
       0 不含税费用金额变化量交易币,
       0 费用税额变化量交易币,
       f.currency 费用币种,
       f.tax_rate 税率,
       case
           when f.ccaltype = '0' then
               round(a.totalextax_premium_amount_cny * f.fee_rate, 2)
           else
               round(a.totalpremium_amount_cny * f.fee_rate, 2)
           end as 费用金额变化量本位币,
       0 不含税费用金额变化量本位币,
       0 费用税额变化量本位币,
       f.ccaltype 含税保费计算方式,
       f.is_fixed_rate,
       null,
       null,
       null,
       'N'
from UDM_P_THIRDPREMIUMTEMP a, udm_policy_sales_fee f
where a.second_main_id = f.main_id
  and a.reflag = 'N'
  and f.endor_no = v_endor_no;
--费用批改
elsif v_endor_type in ('1010', '1040') then
select count(1)
into v_exist_count
from (select cc.policy_no, cc.endor_sn, cc.id
      from udm_p_thirdpremium cc
      where cc.policy_no = v_policy_no
      union all
      select dd.policy_no, dd.endor_sn, dd.id
      from udm_p_thirdpremium_archive dd
      where dd.policy_no = v_policy_no) a,
     (select cc.main_id
      from udm_p_thirdsales_fee cc
      where cc.policy_no = v_policy_no
      union all
      select dd.main_id
      from udm_p_thirdsales_fee_archive dd
      where dd.policy_no = v_policy_no) c
where c.main_id = a.id
  and a.policy_no = v_policy_no
  and a.endor_sn < v_endor_sn;
if v_exist_count > 0 then
      insert into udm_policy_salesfeetemp
        (policy_no,
         coins_code,
         joins_code,
         kind_sn,
         kind_code,
         OPERATOR_CODE,
         fee_type,
         fee_amout,
         fee_amout_cny)
select a.policy_no,
       a.coins_code,
       a.joins_code,
       a.kind_sn,
       a.kind_code,
       c.OPERATOR_CODE,
       c.fee_type,
       sum(c.fee_amount),
       sum(c.fee_amount_cny)
from (select cc.policy_no,
             cc.endor_sn,
             cc.endor_no,
             cc.id,
             cc.coins_code,
             cc.joins_code,
             cc.kind_sn,
             cc.kind_code
      from udm_p_thirdpremium cc
      where cc.policy_no = v_policy_no
      union all
      select dd.policy_no,
             dd.endor_sn,
             dd.endor_no,
             dd.id,
             dd.coins_code,
             dd.joins_code,
             dd.kind_sn,
             dd.kind_code
      from udm_p_thirdpremium_archive dd
      where dd.policy_no = v_policy_no) a,
     (select cc.main_id,
             cc.operator_code,
             cc.fee_type,
             cc.fee_amount,
             cc.fee_amount_cny
      from udm_p_thirdsales_fee cc
      where cc.policy_no = v_policy_no
      union all
      select dd.main_id,
             dd.operator_code,
             dd.fee_type,
             dd.fee_amount,
             dd.fee_amount_cny
      from udm_p_thirdsales_fee_archive dd
      where dd.policy_no = v_policy_no) c
where c.main_id = a.id
  and a.policy_no = v_policy_no
  -- and c.hedge_thirdid is null and not exists (select 1 from UDM_P_THIRDSALES_FEE d where d.hedge_thirdid=c.id and d.hedge_thirdid is not null)
  and a.endor_sn < v_endor_sn
group by a.policy_no,
         a.coins_code,
         a.joins_code,
         a.kind_sn,
         a.kind_code,
         c.OPERATOR_CODE,
         c.fee_type;

select count(1) into v_count from udm_policy_salesfeetemp;
UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',
                                     sysdate,
                                     v_endor_no,
                                     sqlcode,
                                     '1-历史数据个数=' || to_char(v_count) || '=' ||
                                     v_endor_sn,
                                     'sales');

select count(1)
into v_count
from UDM_P_THIRDPREMIUMTEMP a, udm_policy_sales_fee f
where a.second_main_id = f.main_id
  and a.reflag = 'N'
  and f.endor_no = v_endor_no;
UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',
                                     sysdate,
                                     v_endor_no,
                                     sqlcode,
                                     '2-新数据个数=' || to_char(v_count),
                                     'sales');

insert into UDM_P_THIRDSALES_FEEtemp
(id,
 main_id,
 second_id,
 policy_no,
 endor_no,
 crct_no,
 fee_type,
 operator_code,
 fee_rate,
 fee_amount,
 extax_fee_amount,
 tax_amount,
 currency,
 tax_rate,
 fee_amount_cny,
 extax_fee_amount_cny,
 tax_amount_cny,
 ccaltype,
 is_fixed_rate,
 hedge_sencondid,
 hedge_thirdid,
 taildiff_flag,
 HEDGE_FLAG)
select v_sys_source || v_sysday ||
       LPAD(SEQ_UDM_P_Tsalefeeid.Nextval, 12, '0') ID,
       a.id Main_ID,
       f.id,
       a.policy_no 保单号,
       a.endor_no 批单号,
       f.crct_no,
       f.fee_type,
       f.operator_code 业务员,
       f.fee_rate 费用比例,
       --费用金额变化量=最新责任保费*最新费用比例-历史费用金额变化量合计
       case
           when f.ccaltype = '0' then
               round(a.totalextax_premium_amount * f.fee_rate -
                     tempfee.fee_amout,
                     2)
           else
               round(a.totalpremium_amount * f.fee_rate -
                     tempfee.fee_amout,
                     2)
           end as 费用金额变化量交易币,
       0 不含税费用金额变化量交易币,
       0 费用税额变化量交易币,
       f.currency 费用币种,
       f.tax_rate 税率,
       case
           when f.ccaltype = '0' then
               round((a.totalextax_premium_amount * f.fee_rate -
                      tempfee.fee_amout) * a.premium_exrate,
                     2)
           else
               round((a.totalpremium_amount * f.fee_rate -
                      tempfee.fee_amout) * a.premium_exrate,
                     2)
           end as 费用金额变化量本位币,
       0 不含税费用金额变化量本位币,
       0 费用税额变化量本位币,
       f.ccaltype 含税保费计算方式,
       f.is_fixed_rate,
       null,
       null,
       null,
       'N'
from UDM_P_THIRDPREMIUMTEMP  a,
     udm_policy_sales_fee    f,
     udm_policy_salesfeetemp tempfee
where a.second_main_id = f.main_id
  and nvl(a.coins_code, '0') = nvl(tempfee.coins_code, '0')
  and nvl(a.joins_code, '0') = nvl(tempfee.joins_code, '0')
  and f.operator_code = tempfee.operator_code
  and nvl(a.kind_code, 0) = nvl(tempfee.kind_code, 0)
  and nvl(a.kind_sn, 0) = nvl(tempfee.kind_sn, 0)
  and a.policy_no = tempfee.policy_no
  and f.fee_type = tempfee.fee_type
  and a.reflag = 'N'
  and f.endor_no = v_endor_no;


select count(1) into v_count from UDM_P_THIRDSALES_FEEtemp;
UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',
                                     sysdate,
                                     v_endor_no,
                                     sqlcode,
                                     '3-最新数据个数=' || to_char(v_count),
                                     'sales');
    
      --判断是否有新增的费用类型
select count(1)
into v_count
from udm_policy_sales_fee f
where not exists (select 1
                  from (select cc.policy_no, cc.fee_type
                        from udm_p_thirdsales_fee cc
                        where cc.policy_no = v_policy_no
                        union all
                        select dd.policy_no, dd.fee_type
                        from udm_p_thirdsales_fee_archive dd
                        where dd.policy_no = v_policy_no) b
                  where b.policy_no = f.policy_no
                    and b.fee_type = f.fee_type)
  and f.endor_no = v_endor_no;
if v_count > 0 then
        insert into UDM_P_THIRDSALES_FEEtemp
          (id,
           main_id,
           second_id,
           policy_no,
           endor_no,
           crct_no,
           fee_type,
           operator_code,
           fee_rate,
           fee_amount,
           extax_fee_amount,
           tax_amount,
           currency,
           tax_rate,
           fee_amount_cny,
           extax_fee_amount_cny,
           tax_amount_cny,
           ccaltype,
           is_fixed_rate,
           hedge_sencondid,
           hedge_thirdid,
           taildiff_flag,
           HEDGE_FLAG)
select v_sys_source || v_sysday ||
       LPAD(SEQ_UDM_P_Tsalefeeid.Nextval, 12, '0') ID,
       a.id Main_ID,
       f.id,
       a.policy_no 保单号,
       a.endor_no 批单号,
       f.crct_no,
       f.fee_type,
       f.operator_code 业务员,
       f.fee_rate 费用比例,
       case
           when f.ccaltype = '0' then
               round(a.totalextax_premium_amount * f.fee_rate, 2)
           else
               round(a.totalpremium_amount * f.fee_rate, 2)
           end as 费用金额变化量交易币,
       0 不含税费用金额变化量交易币,
       0 费用税额变化量交易币,
       f.currency 费用币种,
       f.tax_rate 税率,
       case
           when f.ccaltype = '0' then
               round(a.totalextax_premium_amount_cny * f.fee_rate, 2)
           else
               round(a.totalpremium_amount_cny * f.fee_rate, 2)
           end as 费用金额变化量本位币,
       0 不含税费用金额变化量本位币,
       0 费用税额变化量本位币,
       f.ccaltype 含税保费计算方式,
       f.is_fixed_rate,
       null,
       null,
       null,
       'N'
from UDM_P_THIRDPREMIUMTEMP a, udm_policy_sales_fee f
where a.second_main_id = f.main_id
  and not exists (select 1
                  from (select cc.policy_no, cc.fee_type
                        from udm_p_thirdsales_fee cc
                        where cc.policy_no = v_policy_no
                        union all
                        select dd.policy_no, dd.fee_type
                        from udm_p_thirdsales_fee_archive dd
                        where dd.policy_no = v_policy_no) b
                  where b.policy_no = f.policy_no
                    and b.fee_type = f.fee_type)
  and a.reflag = 'N'
  and f.endor_no = v_endor_no;
end if;

else
      --DBMS_OUTPUT.PUT_LINE('执行临时表保存');
      insert into UDM_P_THIRDSALES_FEEtemp
        (id,
         main_id,
         second_id,
         policy_no,
         endor_no,
         crct_no,
         fee_type,
         operator_code,
         fee_rate,
         fee_amount,
         extax_fee_amount,
         tax_amount,
         currency,
         tax_rate,
         fee_amount_cny,
         extax_fee_amount_cny,
         tax_amount_cny,
         ccaltype,
         is_fixed_rate,
         hedge_sencondid,
         hedge_thirdid,
         taildiff_flag,
         HEDGE_FLAG)
select v_sys_source || v_sysday ||
       LPAD(SEQ_UDM_P_Tsalefeeid.Nextval, 12, '0') ID,
       a.id Main_ID,
       f.id,
       a.policy_no 保单号,
       a.endor_no 批单号,
       f.crct_no,
       f.fee_type,
       f.operator_code 业务员,
       f.fee_rate 费用比例,
       case
           when f.ccaltype = '0' then
               round(a.totalextax_premium_amount * f.fee_rate, 2)
           else
               round(a.totalpremium_amount * f.fee_rate, 2)
           end as 费用金额变化量交易币,
       0 不含税费用金额变化量交易币,
       0 费用税额变化量交易币,
       f.currency 费用币种,
       f.tax_rate 税率,
       case
           when f.ccaltype = '0' then
               round(a.totalextax_premium_amount_cny * f.fee_rate, 2)
           else
               round(a.totalpremium_amount_cny * f.fee_rate, 2)
           end as 费用金额变化量本位币,
       0 不含税费用金额变化量本位币,
       0 费用税额变化量本位币,
       f.ccaltype 含税保费计算方式,
       f.is_fixed_rate,
       null,
       null,
       null,
       'N'
from UDM_P_THIRDPREMIUMTEMP a, udm_policy_sales_fee f
where a.second_main_id = f.main_id
  and a.reflag = 'N'
  and f.endor_no = v_endor_no;
end if;
      
  
  elsif v_count > 0 then
    --1.2.其他
    insert into UDM_P_THIRDSALES_FEEtemp
      (id,
       main_id,
       second_id,
       policy_no,
       endor_no,
       crct_no,
       fee_type,
       operator_code,
       fee_rate,
       fee_amount,
       extax_fee_amount,
       tax_amount,
       currency,
       tax_rate,
       fee_amount_cny,
       extax_fee_amount_cny,
       tax_amount_cny,
       ccaltype,
       is_fixed_rate,
       hedge_sencondid,
       hedge_thirdid,
       taildiff_flag,
       HEDGE_FLAG)
select v_sys_source || v_sysday ||
       LPAD(SEQ_UDM_P_Tsalefeeid.Nextval, 12, '0') ID,
       a.id Main_ID,
       f.id,
       a.policy_no 保单号,
       a.endor_no 批单号,
       f.crct_no,
       f.fee_type,
       f.operator_code,
       f.fee_rate 费用比例,
       case
           when f.ccaltype = '0' then
               round(a.extax_premium_amount * f.fee_rate, 2)
           else
               round(a.premium_amount * f.fee_rate, 2)
           end as 费用金额变化量交易币,
       0 不含税费用金额变化量交易币,
       0 费用税额变化量交易币,
       f.currency 费用币种,
       f.tax_rate,
       case
           when f.ccaltype = '0' then
               round(a.extax_premium_amount_cny * f.fee_rate, 2)
           else
               round(a.premium_amount_cny * f.fee_rate, 2)
           end as 费用金额变化量本位币,
       0 不含税费用金额变化量本位币,
       0 费用税额变化量本位币,
       f.ccaltype 含税保费计算方式,
       f.is_fixed_rate,
       null,
       null,
       null,
       'N'
from UDM_P_THIRDPREMIUMTEMP a, udm_policy_sales_fee f
where a.second_main_id = f.main_id
  and a.policy_no = f.policy_no
  and a.endor_no = f.endor_no
  and a.reflag = 'N'
  and a.policy_no = v_policy_no
  and a.endor_no = v_endor_no;
end if;


select count(1)
into v_count
from UDM_P_THIRDSALES_FEEtemp a
where a.hedge_flag = 'N'
  and a.endor_no = v_endor_no;

if v_count > 0 and 1 = 2 then
    --2、尾差处理,需要按照费用类型进行尾差处理
    if v_f_count > 0 and v_z_count = 0 then
      --只冲负，冲正重新算的情况，尾差按最新值算
      for p in (select distinct a.policy_no, a.endor_no, a.fee_type
                  from UDM_P_THIRDSALES_FEEtemp a
                 where a.hedge_flag = 'N'
                   and a.endor_no = v_endor_no) loop
select (aa.newfee_amount - bb.oldfee_amount) fee_amount,
       (aa.newfee_amount_cny - bb.oldfee_amount_cny) fee_amount_cny
into v_fee_amount, v_fee_amount_cny
from (select sum(a.fee_amount) newfee_amount,
             sum(a.fee_amount_cny) newfee_amount_cny
      from UDM_P_THIRDSALES_FEEtemp a
      where a.hedge_flag = 'N'
        and a.endor_no = p.endor_no
        and a.fee_type = p.fee_type
      group by a.endor_no) aa,
     (select sum(a.fee_amount) oldfee_amount,
             sum(a.fee_amount_cny) oldfee_amount_cny
      from Udm_Policy_Sales_Fee a, udm_policy_main b
      where a.endor_no = b.endor_no
        and b.endor_sn <= v_endor_sn
        and a.policy_no = p.policy_no
        and a.fee_type = p.fee_type
      group by a.policy_no) bb;

if v_fee_amount != 0 or v_fee_amount_cny != 0 then
select count(1)
into v_count
from UDM_P_THIRDSALES_FEEtemp feetemp
where feetemp.endor_no = p.endor_no
  and feetemp.fee_type = p.fee_type
  and feetemp.fee_amount != 0
             and feetemp.hedge_flag = 'N';
if v_count > 0 then
select mid
into v_mainid
from (select feetemp.id mid
      from UDM_P_THIRDSALES_FEEtemp feetemp
      where feetemp.endor_no = p.endor_no
        and feetemp.fee_type = p.fee_type
        and feetemp.fee_amount != 0
                       and feetemp.hedge_flag = 'N'
      order by feetemp.id desc)
where rownum = 1;

update UDM_P_THIRDSALES_FEEtemp tt
set tt.fee_amount     = tt.fee_amount - v_fee_amount,
    tt.fee_amount_cny = tt.fee_amount_cny - v_fee_amount_cny,
    tt.taildiff_flag  = 'Y'
where tt.id = v_mainid;
end if;
end if;
end loop;
else
    
      for p in (select distinct a.endor_no, a.fee_type
                  from UDM_P_THIRDSALES_FEEtemp a
                 where a.hedge_flag = 'N'
                   and a.endor_no = v_endor_no) loop
select (aa.newfee_amount - bb.oldfee_amount) fee_amount,
       (aa.newfee_amount_cny - bb.oldfee_amount_cny) fee_amount_cny
into v_fee_amount, v_fee_amount_cny
from (select sum(a.fee_amount) newfee_amount,
             sum(a.fee_amount_cny) newfee_amount_cny
      from UDM_P_THIRDSALES_FEEtemp a
      where a.hedge_flag = 'N'
        and a.endor_no = p.endor_no
        and a.fee_type = p.fee_type
      group by a.endor_no) aa,
     (select sum(a.fee_amount) oldfee_amount,
             sum(a.fee_amount_cny) oldfee_amount_cny
      from Udm_Policy_Sales_Fee a
      where a.endor_no = p.endor_no
        and a.fee_type = p.fee_type
      group by a.endor_no) bb;

if v_fee_amount != 0 or v_fee_amount_cny != 0 then
select count(1)
into v_count
from UDM_P_THIRDSALES_FEEtemp feetemp
where feetemp.endor_no = p.endor_no
  and feetemp.fee_type = p.fee_type
  and feetemp.fee_amount != 0
             and feetemp.hedge_flag = 'N';
if v_count > 0 then
select mid
into v_mainid
from (select feetemp.id mid
      from UDM_P_THIRDSALES_FEEtemp feetemp
      where feetemp.endor_no = p.endor_no
        and feetemp.fee_type = p.fee_type
        and feetemp.fee_amount != 0
                       and feetemp.hedge_flag = 'N'
      order by feetemp.id desc)
where rownum = 1;

update UDM_P_THIRDSALES_FEEtemp tt
set tt.fee_amount     = tt.fee_amount - v_fee_amount,
    tt.fee_amount_cny = tt.fee_amount_cny - v_fee_amount_cny,
    tt.taildiff_flag  = 'Y'
where tt.id = v_mainid;
end if;
end if;
end loop;

end if;
end if;

  --统一处理：不含税费用金额变化量 = 费用金额变化量
update UDM_P_THIRDSALES_FEEtemp a
set a.extax_fee_amount     = a.fee_amount,
    a.extax_fee_amount_cny = a.fee_amount_cny
where a.endor_no = v_endor_no;

Exception
  when others then
    UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdsalefeeexpand',
                                   sysdate,
                                   v_endor_no,
                                   sqlcode,
                                   DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || '--' ||
                                   substr(sqlerrm, 1, 1000),
                                   'udm_p_thirdsalefeeexpand');
    raise;
end;
